package com.milling.web.controller.business;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.milling.business.BusinessConstants;
import com.milling.business.domain.WmsConfigInfo;
import com.milling.business.domain.WmsErpTask;
import com.milling.business.service.*;
import com.milling.common.core.controller.BaseController;
import com.milling.common.core.domain.AjaxResult;
import com.milling.common.core.redis.RedisCache;
import com.milling.common.utils.sql.DbUtils;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import reactor.core.publisher.Flux;

import javax.servlet.http.HttpServletRequest;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 对外接口信息Controller
 *
 * @author X
 * @date 2025-01-17
 */
@RestController
@RequestMapping("/erpApi")
public class WmsApiController extends BaseController
{
    @Autowired
    private IWmsErpTaskService wmsErpTaskService;

    @Autowired
    private IWmsTaskInfoService wmsTaskInfoService;

    @Autowired
    private HttpServletRequest httpServletRequest;

    @Autowired
    private IWmsShelfInfoService wmsShelfInfoService;

    @Autowired
    private IWmsCellInfoService wmsCellInfoService;

    @Autowired
    private RedisCache redisCache;

    @Autowired
    private DbUtils dbUtils;

    @Autowired
    private IWmsNeedleCacheService wmsNeedleCacheService;

    @Autowired
    private IWmsDrillinfoStockService wmsDrillinfoStockService;;

    @Autowired
    private IWmsConfigInfoService wmsConfigInfoService;

    /**
     * 接收配针任务
     * @param jsonString
     * @return
     */
    @PostMapping("/createNeedleTask")
    @ApiOperation(value = "配针任务接口", notes = "预配针接口")
    public AjaxResult createNeedleTask(@RequestBody String jsonString) {
        long startTime = System.currentTimeMillis(); // 记录开始时间
        logger.info("IP is: " + getIpAddress(httpServletRequest));

        logger.info(jsonString);
        String jsonStringSrc = jsonString;
        AjaxResult ajaxResult = AjaxResult.success();
        WmsErpTask wmsErpTask = new WmsErpTask();
        String msg = "接收成功";
        int code = 200;
        int axles = 0;
        String itemCode = "";
        List list = new ArrayList();
        Map toolMap = new HashMap();
        try {
            JSONObject jsonObject = JSONObject.parseObject(jsonString);
            String macCode = jsonObject.getString("macCode");
            String transCode = jsonObject.getString("transCode");
            axles = jsonObject.getIntValue("axles");
            itemCode = jsonObject.getString("itemCode");
            logger.info("macCode: " + macCode + ", transCode: " + transCode + ", axles: " + axles);
            JSONArray drillArrays = jsonObject.getJSONArray("drillArray");
            //临时注释
            //将后库的库存数据保存到本地数据库
            wmsDrillinfoStockService.syncStock();
            for (int i = 0; i < drillArrays.size(); i++) {
                JSONObject drillObject = drillArrays.getJSONObject(i);
                String tools = drillObject.getString("tools");
                String drillSize = drillObject.getString("drillSize");
                String holeType = drillObject.getString("holeType");
                int drillCount = drillObject.getIntValue("drillCount");
                int drillGrindCount = drillObject.getIntValue("drillGrindCount");
                logger.info("tools: " + tools + ", drillSize: " + drillSize + ", holeType: " + holeType + ", drillCount: " + drillCount + ", drillGrindCount: " + drillGrindCount);
                String drillinfo = getDrillInfoByGrindCount(drillSize, holeType, drillGrindCount, redisCache);
                logger.info("根据尺寸 和 研次计算出来真实可配针的料号 drillinfo: " + drillinfo);
                if (drillinfo != null && !drillinfo.startsWith("Error")) {
                    drillObject.put("drillInfo", drillinfo);
                    drillObject.put("realGrindCount", drillinfo.substring(7,8));
                } else {
                    msg = "钻孔规格不存在";
                    code = 202;
                    wmsErpTask.setStatus(BusinessConstants.TASK_STATUS_3);
                    wmsErpTask.setRetCode("202");
                    wmsErpTask.setMsg("钻孔规格不存在");
                    ajaxResult.put("msg", msg);
                    ajaxResult.put("code", code);
                    return ajaxResult;
                }
            }

            // 更新 jsonStringSrc 以包含 drillInfo
            jsonStringSrc = jsonObject.toJSONString();

            //处理流程
            ajaxResult.put("macCode", macCode);
            ajaxResult.put("transCode", transCode);
            logger.info("jsonString drillinfo: " + jsonString);
            logger.info("jsonStringSrc drillinfo: " + jsonStringSrc);
            wmsErpTask.setConfigData(jsonStringSrc);
            wmsErpTask.setMacCode(macCode);
            wmsErpTask.setTransCode(transCode);
            wmsErpTask.setStatus(BusinessConstants.TASK_STATUS_1);

            /// //////////////////解析刀具表 和 备用刀盘 start
            Map<String, Object> map = parseTools(jsonStringSrc);
            list = (List)map.get("boxdataList");
            toolMap = (Map<String, Object>)map.get("toolMap");
            JsonArray fullBoxList = (JsonArray)map.get("fullBoxList");
//            String fullboxStr = fullBoxList.toString().replace("[", "").replace("]", "");
            String fullboxStr = fullBoxList.toString();
            wmsErpTask.setFullboxData(fullboxStr);
            /// //////////////////解析刀具表 和 备用刀盘 end
//            wmsErpTaskService.insertWmsErpTask(wmsErpTask);
        } catch (Exception e) {
            code = 202;
            msg = "数据格式异常";
            wmsErpTask.setStatus(BusinessConstants.TASK_STATUS_3);
            wmsErpTask.setRetCode("201");
            wmsErpTask.setMsg("数据格式异常");
        }
        ajaxResult.put("msg", msg);
        ajaxResult.put("code", code);
        wmsErpTask.setRetCode("" + code);
        wmsErpTask.setMsg(msg);
        wmsErpTask.setReqBody(jsonString);
//        wmsErpTask.setReqBody(jsonStringSrc);
        wmsErpTask.setResBody(ajaxResult.toString());
        logger.info(ajaxResult.toString());

        wmsErpTaskService.insertWmsErpTask(wmsErpTask);

//        list.addAll(list);
        String configContent = "";
/*        try {
            configContent = list.toString().replace("[", "").replace("]", "");
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("配针数据解析异常");
        }*/
        configContent = backupBoxesToString(list, toolMap).replace("\"", "");

        logger.info("list is" + configContent);
        WmsConfigInfo wmsConfigInfo = new WmsConfigInfo();
        wmsConfigInfo.setConfigStatus(BusinessConstants.CONFIG_STATUS_UNCONFIG);
        wmsConfigInfo.setSupplyStatus(BusinessConstants.TASK_STATUS_1);//待处理

        wmsConfigInfo.setErpTaskId(wmsErpTask.getId());
        wmsConfigInfo.setAxles(new Long(axles));
        wmsConfigInfo.setWorkId(itemCode);
        wmsConfigInfo.setConfigContent(configContent);
        wmsConfigInfoService.insertWmsConfigInfo(wmsConfigInfo);

        long endTime = System.currentTimeMillis(); // 记录结束时间
        long duration = endTime - startTime; // 计算执行时间
        logger.info("postData executed in {} ms", duration); // 输出执行时间

        return ajaxResult;
    }

    public String getDrillInfoByGrindCount(String drillSize, String holeType, int drillGrindCount, RedisCache redisCache) {
        // 如果 drillGrindCount 为负数，直接使用 drillSize 获取数据
        if (drillGrindCount < 0) {
            return getDrillInfoBySize(drillSize, holeType, redisCache);
        }

        // 尝试获取数据，如果获取不到则递减 drillGrindCount 继续尝试
        while (drillGrindCount >= 0) {
            String key = "DRILLTYPE-GRINDCOUNT-" + drillSize + "-" + holeType + "-" + drillGrindCount;
            Object drillinfo = redisCache.getCacheObject(key);

            if (drillinfo != null) {
                return drillinfo.toString();
            }

            logger.info("Drill info not found for size: {}, grind count: {}", drillSize, drillGrindCount);
            drillGrindCount--; // 继续尝试 drillGrindCount - 1
        }

        // 如果所有尝试都失败，返回错误信息
        return "Error: 没有钻针尺寸为 " + drillSize + " 的钻针";
    }

    private String getDrillInfoBySize(String drillSize, String holeType, RedisCache redisCache) {
        String key = "DRILLTYPE-" + drillSize + "-" + holeType;
        Object drillinfo = redisCache.getCacheObject(key);

        if (drillinfo == null) {
            logger.info("Drill info not found for size: {}", drillSize);
            return "Error: 没有钻针尺寸为 " + drillSize + " 的钻针";
        }

        return drillinfo.toString();
    }

    public Map<String, Object> parseTools(String jsonString) {
        List list = new ArrayList();

        // 解析JSON
        JsonObject root2 = JsonParser.parseString(jsonString).getAsJsonObject();
        JsonArray drillArray2 = root2.getAsJsonArray("drillArray");

        // 处理drillArray
        processDrillArray(drillArray2);

        // 输出结果
        System.out.println(root2);

        Map toolMap = new HashMap();
        // ===== 新增：生成整盒列表 =====
        JsonArray fullBoxList = new JsonArray();
        for (int i = 0; i < drillArray2.size(); i++) {
            JsonObject tool = drillArray2.get(i).getAsJsonObject();
            int drillCount = tool.get("drillCount").getAsInt();

            toolMap.put(tool.get("tools").getAsString(), tool.get("drillInfo"));

            // 计算整盒数量和剩余数量
            int fullBoxCount = drillCount / 50;  // 整盒数量（50支/盒）
            int remainder = drillCount % 50;     // 剩余数量

            // 如果整盒数量大于0，则添加到整盒列表
            if (fullBoxCount > 0) {
                JsonObject boxInfo = new JsonObject();
                boxInfo.addProperty("drillInfo", tool.get("drillInfo").getAsString());
                boxInfo.addProperty("drillSize", tool.get("drillSize").getAsString());
                boxInfo.addProperty("drillGrindCount", tool.get("drillGrindCount").getAsInt());
                boxInfo.addProperty("count", fullBoxCount);
                fullBoxList.add(boxInfo);
            }

            // 更新原始数据中的数量为剩余数量
            tool.addProperty("drillCount", remainder);
        }
        System.out.println(root2);
        System.out.println("整盒直通下料料号信息：" + fullBoxList.toString());


        JsonNode drillArray = null;

        try {
            ObjectMapper mapper = new ObjectMapper();
            JsonNode root = mapper.readTree(root2.toString());

            drillArray = root.get("drillArray");
        } catch (JsonProcessingException e) {
            logger.error("解析JSON对象异常");
            e.printStackTrace();
        }

        // 主刀盒处理
        List<String[][]> mainBoxes = generateMainBoxes(drillArray);
        // 备用刀盒处理
        List<String[][]> backupBoxes = generateBackupBoxes(drillArray);

        // 打印结果
/*        System.out.println("主刀盒数量: " + mainBoxes.size());
        for (int i = 0; i < mainBoxes.size(); i++) {
            System.out.println("主刀盒 " + (i + 1) + ":");
            List list2 = printBoxMapToList(mainBoxes.get(i));
            list.addAll(list2);
        }

        System.out.println("备用刀盒数量: " + backupBoxes.size());
        for (int i = 0; i < backupBoxes.size(); i++) {
            System.out.println("备用刀盒 " + (i + 1) + ":");
            List list3 = printBoxMapToList(backupBoxes.get(i));
            list.addAll(list3);
        }*/

        list.addAll(mainBoxes);
        list.addAll(backupBoxes);
        Map<String, Object> map = new HashMap<>();
        map.put("boxdataList", list);
        map.put("fullBoxList", fullBoxList);
        map.put("toolMap", toolMap);

        return map;
    }

    private List printBoxMapToList(Map<String, Map<String, String>> boxMap) {
        List list = new ArrayList();
        for (int col = 0; col < 5; col++) {
            for (int row = 0; row < 10; row++) {
//            for (int col = 0; col < 5; col++) {
//                Map<String, String> toolDetails = boxMap.get(col + "-" + row);
                Map<String, String> toolDetails = boxMap.get(row + "-" + col);
                if (!toolDetails.isEmpty()) {
//                    System.out.print(toolDetails + "\t");
//                    System.out.print(toolDetails.get("tools") + "-" + toolDetails.get("drillSize") + "-" + toolDetails.get("drillGrindCount") + "-" + toolDetails.get("holeType") + "-" + toolDetails.get("drillInfo") + "\t");
                    list.add(toolDetails.get("drillInfo"));
                    System.out.print(toolDetails.get("tools") + "-" + toolDetails.get("drillInfo") + "\t");
                } else {
                    list.add("");
//                    System.out.print("{            }\t");
//                    System.out.print("                        \t");
                    System.out.print("                \t");
                }
            }
            System.out.println();
        }
        System.out.println();
        return list;
    }

    private static List<String[][]> generateMainBoxes(JsonNode drillArray) {
        List<String[][]> mainBoxes = new ArrayList<>();
        List<String> mainTools = new ArrayList<>();

        // 收集主刀盒中的刀具
        for (JsonNode tool : drillArray) {
            mainTools.add(tool.get("tools").asText());
        }

        // 计算需要多少个主刀盒
        int mainBoxCount = (int) Math.ceil(mainTools.size() / 50.0);
        for (int i = 0; i < mainBoxCount; i++) {
            String[][] box = new String[10][5]; // 10行5列
            int startIndex = i * 50;
            int endIndex = Math.min(startIndex + 50, mainTools.size());

            for (int j = 0; j < 5; j++) { // 5列
                for (int k = 0; k < 10; k++) { // 10行
                    int toolIndex = startIndex + j * 10 + k;
                    if (toolIndex < endIndex) {
                        box[k][j] = mainTools.get(toolIndex);
                    } else {
                        box[k][j] = "";
                    }
                }
            }
            mainBoxes.add(box);
        }

        return mainBoxes;
    }

    private static List<String[][]> generateBackupBoxes(JsonNode drillArray) {
        List<String[][]> backupBoxes = new ArrayList<>();
        List<String> currentBox = new ArrayList<>(50); // 当前正在填充的备用刀盒
        List<String> currentColumn = new ArrayList<>(); // 当前正在填充的列

        // 遍历每种刀具
        for (JsonNode tool : drillArray) {
            String toolId = tool.get("tools").asText();
            int remainingCount = tool.get("drillCount").asInt() - 1; // 减去主刀盒中的一支

            // 如果没有剩余数量，跳过
            if (remainingCount <= 0) {
                continue;
            }

            // 检查是否为平衡刀具（需要独立占用一盒）
            boolean isBalanceTool = tool.has("balanceFlag") && tool.get("balanceFlag").asBoolean();

            // 如果是平衡刀具，独立占用一盒
            if (isBalanceTool) {
                // 先完成当前正在填充的盒子（如果有）
                if (!currentColumn.isEmpty()) {
                    fillColumn(currentBox, currentColumn);
                }
                if (!currentBox.isEmpty()) {
                    backupBoxes.add(convertToListArray(currentBox));
                    currentBox = new ArrayList<>(50);
                }

                // 创建独立盒子
                List<String> balanceBox = new ArrayList<>(50);
                for (int i = 0; i < remainingCount; i++) {
                    balanceBox.add(toolId);
                }
                // 填充独立盒子
                String[][] boxArray = createBoxForBalanceTool(balanceBox);
                backupBoxes.add(boxArray);
                continue; // 处理下一个刀具
            }

            // 非平衡刀具：正常填充
            for (int i = 0; i < remainingCount; i++) {
                // 如果当前列已满，切换到下一列
                if (currentColumn.size() >= 10) {
                    fillColumn(currentBox, currentColumn);

                    // 如果当前刀盒已满，创建新的刀盒
                    if (currentBox.size() >= 50) {
                        backupBoxes.add(convertToListArray(currentBox));
                        currentBox = new ArrayList<>(50);
                    }
                }

                // 添加刀具到当前列
                currentColumn.add(toolId);

                // 如果是最后一个刀具，填充剩余位置为空字符串
                if (i == remainingCount - 1) {
                    fillColumn(currentBox, currentColumn);
                }
            }

            // 检查是否需要切换到下一列
            if (!currentColumn.isEmpty() && currentColumn.size() >= 10) {
                fillColumn(currentBox, currentColumn);
            }

            // 如果当前刀盒已满，创建新的刀盒
            if (currentBox.size() >= 50) {
                backupBoxes.add(convertToListArray(currentBox));
                currentBox = new ArrayList<>(50);
            }
        }

        // 处理剩余的刀具
        if (!currentColumn.isEmpty()) {
            fillColumn(currentBox, currentColumn);
        }

        // 处理剩余的刀盒
        if (!currentBox.isEmpty()) {
            backupBoxes.add(convertToListArray(currentBox));
        }

        return backupBoxes;
    }

    // 填充列到盒子
    private static void fillColumn(List<String> currentBox, List<String> currentColumn) {
        while (currentColumn.size() < 10) {
            currentColumn.add("");
        }
        currentBox.addAll(currentColumn);
        currentColumn.clear();
    }

    // 为平衡刀具创建独立盒子
    private static String[][] createBoxForBalanceTool(List<String> toolList) {
        String[][] box = new String[10][5];
        int count = 0;

        for (int col = 0; col < 5; col++) {
            for (int row = 0; row < 10; row++) {
                if (count < toolList.size()) {
                    box[row][col] = toolList.get(count);
                    count++;
                } else {
                    box[row][col] = "";
                }
            }
        }
        return box;
    }
    private static List<String[][]> generateBackupBoxesII(JsonNode drillArray) {
        List<String[][]> backupBoxes = new ArrayList<>();
        List<String> currentBox = new ArrayList<>(50); // 当前正在填充的备用刀盒
        List<String> currentColumn = new ArrayList<>(); // 当前正在填充的列

        // 遍历每种刀具
        for (JsonNode tool : drillArray) {
            String toolId = tool.get("tools").asText();
            int remainingCount = tool.get("drillCount").asInt() - 1; // 减去主刀盒中的一支

            // 如果没有剩余数量，跳过
            if (remainingCount <= 0) {
                continue;
            }

            // 填充备用刀具到当前列
            for (int i = 0; i < remainingCount; i++) {
                // 如果当前列已满，切换到下一列
                if (currentColumn.size() >= 10) {
                    // 将当前列添加到当前刀盒
                    while (currentColumn.size() < 10) {
                        currentColumn.add("");
                    }
                    currentBox.addAll(currentColumn);
                    currentColumn = new ArrayList<>();

                    // 如果当前刀盒已满，创建新的刀盒
                    if (currentBox.size() >= 50) {
                        String[][] boxArray = convertToListArray(currentBox);
                        backupBoxes.add(boxArray);
                        currentBox = new ArrayList<>(50);
                    }
                }

                // 添加刀具到当前列
                currentColumn.add(toolId);

                // 如果是最后一个刀具，填充剩余位置为空字符串
                if (i == remainingCount - 1) {
                    while (currentColumn.size() < 10) {
                        currentColumn.add("");
                    }
                    currentBox.addAll(currentColumn);
                    currentColumn = new ArrayList<>();
                }
            }

            // 如果当前列不为空，检查是否需要切换到下一列
            if (!currentColumn.isEmpty() && currentColumn.size() >= 10) {
                while (currentColumn.size() < 10) {
                    currentColumn.add("");
                }
                currentBox.addAll(currentColumn);
                currentColumn = new ArrayList<>();
            }

            // 如果当前刀盒已满，创建新的刀盒
            if (currentBox.size() >= 50) {
                String[][] boxArray = convertToListArray(currentBox);
                backupBoxes.add(boxArray);
                currentBox = new ArrayList<>(50);
            }
        }

        // 处理剩余的刀具
        if (!currentColumn.isEmpty()) {
            while (currentColumn.size() < 10) {
                currentColumn.add("");
            }
            currentBox.addAll(currentColumn);
        }

        // 处理剩余的刀盒
        if (!currentBox.isEmpty()) {
            String[][] boxArray = convertToListArray(currentBox);
            backupBoxes.add(boxArray);
        }

        return backupBoxes;
    }

    private static String[][] convertToListArray(List<String> boxList) {
        String[][] box = new String[10][5];
        for (int col = 0; col < 5; col++) {
            for (int row = 0; row < 10; row++) {
                int index = col * 10 + row;
                box[row][col] = index < boxList.size() ? boxList.get(index) : "";
            }
        }
        return box;
    }

    private static void printBox(String[][] box) {
        for (int i = 0; i < 10; i++) {
            for (int j = 0; j < 5; j++) {
                System.out.print(box[i][j] + "\t");
            }
            System.out.println();
        }
        System.out.println();
    }

    private static void processDrillArray(JsonArray drillArray) {
        for (int i = 0; i < drillArray.size(); i++) {
            JsonObject currentObj = drillArray.get(i).getAsJsonObject();

            // 检查是否已经处理过（作为基准对象）
            if (currentObj.has("balanceFlag")) {
                continue;
            }

            String drillInfo = currentObj.get("drillInfo").getAsString();
//            String currentSize = currentObj.get("drillSize").getAsString();
//            int currentGrind = currentObj.get("drillGrindCount").getAsInt();
            int baseCount = currentObj.get("drillCount").getAsInt();

            // 遍历后续对象进行比较
            for (int j = i + 1; j < drillArray.size(); j++) {
                JsonObject compareObj = drillArray.get(j).getAsJsonObject();

                // 跳过已标记为基准的对象
                if (compareObj.has("balanceFlag")) {
                    continue;
                }

                String compareInfo = compareObj.get("drillInfo").getAsString();
//                String compareSize = compareObj.get("drillSize").getAsString();
//                int compareGrind = compareObj.get("drillGrindCount").getAsInt();
                int compareCount = compareObj.get("drillCount").getAsInt();

                // 检查是否匹配
                if (drillInfo.equals(compareInfo)) {
                    // 更新基准对象的drillCount
                    currentObj.addProperty("drillCount", baseCount + compareCount - 1);

                    // 添加平衡标志
                    currentObj.addProperty("balanceFlag", true);

                    // 更新比较对象的drillCount
                    compareObj.addProperty("drillCount", 1);

                    // 更新基准计数用于后续匹配
                    baseCount = baseCount + compareCount - 1;
                }
            }
        }
    }

    /**
     * 校验配针任务库存
     * @param jsonString
     * @return
     */
    @PostMapping("/checkStock")
    @ApiOperation(value = "预配针接口", notes = "预配针接口")
    public AjaxResult checkStock(@RequestBody String jsonString) {
        long startTime = System.currentTimeMillis(); // 记录开始时间
        logger.info("IP is: " + getIpAddress(httpServletRequest));

        logger.info(jsonString);
        JSONObject jsonObject = JSONObject.parseObject(jsonString);
        String macCode = jsonObject.getString("MacCode");
        String drillinfos = jsonObject.getString("drillinfos");

        String message = "ok";
        if("ok".equals(message)) {
            return AjaxResult.success();
        } else {
            return AjaxResult.error(message);
        }
    }

    @PostMapping("/testSql")
    public AjaxResult testSql() {
        logger.info("IP is: " + getIpAddress(httpServletRequest) + ", SQL is: " );
        String sql = generalFlatSql();
        try {
            Map<String, Long> row = new HashMap();
            List<Map<String, Object>> result = dbUtils.executeQuery(sql);
            for (int i = 0; i < result.size(); i++) {
                Map<String, Object> map = result.get(i);
//                logger.info(result.get(i).toString());

                Long drillCount = ((BigDecimal)map.get("totalCount")).longValue();
                String drillSize = (String)map.get("drill_size");
                String drillType = (String)map.get("drill_type");
                String drillEdge = (String)map.get("drill_edge");
                String drillFactory = (String)map.get("drill_factory");
                String drillGrindCount = map.get("drill_grind_count").toString();
                String key = drillSize + "-" + drillType + "-" + drillEdge + "-" + drillFactory + "-" + drillGrindCount;

                row.put(key, drillCount);
            }
            logger.info(row.toString());
            return AjaxResult.success(result);
        } catch (Exception e) {
            logger.error("Error executing SQL: {}", sql, e);
            return AjaxResult.error("Error executing SQL: " + e.getMessage());
        }
    }

    /**
     * 打开 Apifox。
     * 选择 POST 请求方法。
     * 输入 URL：http://localhost:8089/api/executeSql。
     * 在 Headers 标签中添加 Content-Type: application/json。
     * 在 Body 标签中选择 raw 和 JSON 格式，输入 SQL 语句："SELECT * FROM wms_cell_info"
     * @param sql
     * @return
     */
    @PostMapping("/executeSql")
    public AjaxResult executeSql(@RequestBody String sql) {
        logger.info("IP is: " + getIpAddress(httpServletRequest) + ", SQL is: " + sql);
        try {
            if (sql.trim().toUpperCase().startsWith("SELECT")) {
                List<Map<String, Object>> result = dbUtils.executeQuery(sql);
                return AjaxResult.success(result);
            } else {
                int affectedRows = dbUtils.executeUpdate(sql);
                return AjaxResult.success("SQL executed successfully, affected rows: " + affectedRows);
            }
        } catch (Exception e) {
            logger.error("Error executing SQL: {}", sql, e);
            return AjaxResult.error("Error executing SQL: " + e.getMessage());
        }
    }

    @PostMapping("/batchExecuteSql")
    public AjaxResult batchExecuteSql(@RequestBody String[] sqls) {
        try {
            int[] affectedRows = dbUtils.batchUpdate(sqls);
            return AjaxResult.success("Batch SQL executed successfully, affected rows: " + java.util.Arrays.toString(affectedRows));
        } catch (Exception e) {
            logger.error("Error executing batch SQL: {}", java.util.Arrays.toString(sqls), e);
            return AjaxResult.error("Error executing batch SQL: " + e.getMessage());
        }
    }

    /**
     * 配针缓存区库存数据
     * @return
     */
    private String generalNeedleSql() {
        return "SELECT\n" +
                "\tneedle.drill_size,\n" +
                "\tneedle.drill_type,\n" +
                "\tneedle.drill_edge,\n" +
                "\tneedle.drill_factory,\n" +
                "\tneedle.drill_grind_count,\n" +
                "\tsum( needle.drill_count ) totalCount \n" +
                "FROM\n" +
                "\twms_needle_cache needle \n" +
                "GROUP BY\n" +
                "\tneedle.drill_size,\n" +
                "\tneedle.drill_type,\n" +
                "\tneedle.drill_edge,\n" +
                "\tneedle.drill_factory,\n" +
                "\tneedle.drill_grind_count";
    }

    /**
     * 配针缓存区 + 平面缓存库 库存数据，配针任务工单，跟这里比
     * @return
     */
    private String generalFlatSql() {
        return "SELECT\n" +
                "\ttemp.drill_size,\n" +
                "\ttemp.drill_type,\n" +
                "\ttemp.drill_edge,\n" +
                "\ttemp.drill_factory,\n" +
                "\ttemp.drill_grind_count,\n" +
                "\tsum( temp.drill_count ) totalCount \n" +
                "FROM\n" +
                "\t(\n" +
                "SELECT\n" +
                "\tcell.drill_size,\n" +
                "\tcell.drill_type,\n" +
                "\tcell.drill_edge,\n" +
                "\tcell.drill_factory,\n" +
                "\tcell.drill_grind_count,\n" +
                "\tcell.drill_count \n" +
                "FROM\n" +
                "\twms_cell_info cell UNION ALL\n" +
                "SELECT\n" +
                "\tneedle.drill_size,\n" +
                "\tneedle.drill_type,\n" +
                "\tneedle.drill_edge,\n" +
                "\tneedle.drill_factory,\n" +
                "\tneedle.drill_grind_count,\n" +
                "\tneedle.drill_count \n" +
                "FROM\n" +
                "\twms_needle_cache needle \n" +
                "\t) temp \n" +
                "WHERE\n" +
                "\ttemp.drill_size IS NOT NULL \n" +
                "GROUP BY\n" +
                "\ttemp.drill_size,\n" +
                "\ttemp.drill_type,\n" +
                "\ttemp.drill_edge,\n" +
                "\ttemp.drill_factory,\n" +
                "\ttemp.drill_grind_count";
    }


    // 新增方法：将备用刀盒列表转换为字符串
    private static String backupBoxesToString(List<String[][]> backupBoxes, Map toolMap) {
        StringBuilder sb = new StringBuilder();
//        sb.append("备用刀盒数量: ").append(backupBoxes.size()).append("\n\n");

        for (int i = 0; i < backupBoxes.size(); i++) {
//            sb.append("===== 备用刀盒 ").append(i + 1).append(" =====\n");
            String[][] box = backupBoxes.get(i);

            for (int col = 0; col < 5; col++) {
                for (int row = 0; row < 10; row++) {
                    String tool = box[row][col];
                    // 如果为空则显示为[空]
//                    sb.append(tool == null || tool.isEmpty() ? " " : tool);
                    sb.append(tool == null || tool.isEmpty() ? " " : toolMap.get(tool));
                    sb.append(",");
                }
//                sb.append(","); // 每行结束换行
            }
//            sb.append("\n"); // 每个刀盒之间空一行
        }

        return sb.toString();
    }

}
